package dao.implementations.postgresql.Docente;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.implementations.DBBase;
import dao.interfaces.Docente.IDocente;
import dao.transferObjects.Gae.AlumnoTO;


public class DocenteDB extends DBBase implements IDocente
{
	
	public DocenteDB(Connection connection){
		this.conn = connection;
	}
	
	public AlumnoTO resultSet_a_AlumnoTO_TEMP(ResultSet rs)
	{
		AlumnoTO alumno = new AlumnoTO();
		
		try{
			alumno.setMatricula(rs.getInt("matricula"));
			alumno.setNombre(rs.getString("nombre"));
			alumno.setApellido(rs.getString("apellido"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_AlumnoTO_TEMP : " + e.getMessage());
		}
		
		return alumno;
	}
	
	public int crearCursoTemp(AlumnoTO alumnoCurso){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "INSERT INTO temp_alumnos_curso(nombre, apellido, matricula) VALUES (?, ?, ?)";
		
		try{
			ps = this.conn.prepareStatement(query);
			ps.setString(1, alumnoCurso.getNombre());
			ps.setString(2, alumnoCurso.getApellido());
			ps.setInt(3, alumnoCurso.getMatricula());
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> crearCursoTemp -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public List<AlumnoTO> obtenerCursoTemp() {
		List<AlumnoTO> alumnosCurso = new ArrayList<AlumnoTO>();
		
		String query = "SELECT nombre, apellido, matricula FROM temp_alumnos_curso;";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			pstmt = conn.prepareStatement(query);			
			rs = pstmt.executeQuery();
			while(rs.next()){				
				alumnosCurso.add(this.resultSet_a_AlumnoTO_TEMP(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerAlumnosExcel : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return alumnosCurso;
	}
	
	public int eliminarCursoTemp()
	{
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "DELETE FROM temp_alumnos_curso";
		
		try{
			ps = this.conn.prepareStatement(query);
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> eliminarCursoTemp -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int eliminarDocente(int rut)
	{
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "DELETE FROM docente WHERE rut_docente=?";
		
		try{
			ps = this.conn.prepareStatement(query);
			ps.setInt(1, rut);
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> eliminarDocente -> "+ ex.getMessage());
		}		
		return result;
	}
	
	/*
	private static String CAMPOS_CURSO = " id_curso, anio, semestre, seccion, carrera, rut_docente, " +
										 " nombre_curso, cantidad_alumnos, eliminado ";
	
	private static String CAMPOS_GAE = " id_gae, rut_gae, nombre_gae, id_proyecto, id_curso, "+
								       " cantidad_alumnos, nombre_representante, apellido_representante, "+
								       " tipo_fono, fono, correo, nota1, nota2, nota3, nota_socio, nota_final, "+
								       " eliminado_gae, matricula_representante ";
	
	private static String CAMPOS_ALUMNO = " matricula, id_curso, id_gae, rut_gae, nombre, apellido, tipo_fono, "+ 
									      " fono, correo, nota1, nota2, nota3, nota_socio, auto_evaluacion, "+ 
									      " nota_final, eliminado "; 

	public AlumnoTO resultSet_a_AlumnoTO(ResultSet rs)
	{
		AlumnoTO alumno = new AlumnoTO();
		
		try{
			alumno.setMatricula(rs.getInt("matricula"));
			alumno.setId_curso(rs.getInt("id_curso"));
			alumno.setId_gae(rs.getInt("id_gae"));
			alumno.setRut_gae(rs.getInt("rut_gae"));
			alumno.setNombre(rs.getString("nombre"));
			alumno.setApellido(rs.getString("apellido"));
			alumno.setTipo_fono(rs.getString("tipo_fono"));
			alumno.setFono(rs.getInt("fono"));
			alumno.setCorreo(rs.getString("correo"));
			alumno.setNota1(rs.getDouble("nota1"));
			alumno.setNota2(rs.getDouble("nota2"));
			alumno.setNota3(rs.getDouble("nota3"));
			alumno.setNota_socio(rs.getDouble("nota_socio"));
			alumno.setAuto_evaluacion(rs.getDouble("auto_evaluacion"));
			alumno.setNota_final(rs.getDouble("nota_final"));
			alumno.setEliminado(rs.getBoolean("eliminado"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_AlumnoTO : " + e.getMessage());
		}
		
		return alumno;
	}
	
	public CursoTO resultSet_a_CursoTO(ResultSet rs)
	{
		CursoTO curso = new CursoTO();
		
		try{
			curso.setId_curso(rs.getInt("id_curso"));
			curso.setAnio(rs.getInt("anio"));
			curso.setSemestre(rs.getInt("semestre"));
			curso.setSeccion(rs.getString("seccion"));
			curso.setRut_docente(rs.getInt("rut_docente"));
			curso.setCarrera(rs.getString("carrera"));
			curso.setNombre_curso(rs.getString("nombre_curso"));
			curso.setCantidad_alumnos(rs.getInt("cantidad_alumnos"));
			curso.setEliminado(rs.getBoolean("eliminado"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_CursoTO : " + e.getMessage());
		}
		
		return curso;
	}
	
	public GaeTO resultSet_a_GaeTO(ResultSet rs)
	{
		GaeTO gae = new GaeTO();
		
		try{
			gae.setId_gae(rs.getInt("id_gae"));
			gae.setRut_gae(rs.getInt("rut_gae"));
			gae.setNombre_gae(rs.getString("nombre_gae"));
			gae.setId_proyecto(rs.getInt("id_proyecto"));
			gae.setId_curso(rs.getInt("id_curso"));
			gae.setCtd_alumno(rs.getInt("cantidad_alumnos"));
			gae.setNombreRepresentante(rs.getString("nombre_representante"));
			gae.setApellidoRepresentante(rs.getString("apellido_representante"));
			gae.setTipo_fono(rs.getString("tipo_fono"));
			gae.setFono(rs.getInt("fono"));
			gae.setCorreo(rs.getString("correo"));
			gae.setNota1(rs.getDouble("nota1"));
			gae.setNota2(rs.getDouble("nota2"));
			gae.setNota3(rs.getDouble("nota3"));
			gae.setNota_socio(rs.getDouble("nota_socio"));
			gae.setNota_final(rs.getDouble("nota_final"));
			gae.setEliminado(rs.getBoolean("eliminado_gae"));
			gae.setMatriculaRepresentante(rs.getInt("matricula_representante"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_CursoTO : " + e.getMessage());
		}
		
		return gae;
	}
	
	public int obtenerSgteIDCurso()
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int resp = -1;
		String query = "SELECT nextval('curso_id_curso_seq') as NEXTVAL";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			rs = pstmt.executeQuery();
			
			if(rs.next())
				resp = rs.getInt("NEXTVAL");
			
			pstmt.close();
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en ProyectoDB -> obtenerSgteIDCurso() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}		
		return resp;
	}
	
	public int obtenerSgteIDGae()
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int resp = -1;
		String query = "SELECT nextval('gae_id_gae_seq') as NEXTVAL";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			rs = pstmt.executeQuery();
			
			if(rs.next())
				resp = rs.getInt("NEXTVAL");
			
			pstmt.close();
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en ProyectoDB -> obtenerSgteIDGae() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}		
		return resp;
	}

	public int crearAlumnoCurso(AlumnoTO alumno)
	{
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "INSERT INTO alumno( "+
					   "matricula, id_curso, nombre, apellido, nota1, "+
					   "nota2, nota3, nota_socio, auto_evaluacion, nota_final, eliminado)"+
					   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";	
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, alumno.getMatricula());
			ps.setInt(2, alumno.getId_curso());
			ps.setString(3, alumno.getNombre());
			ps.setString(4, alumno.getApellido());
			ps.setDouble(5, alumno.getNota1());
			ps.setDouble(6, alumno.getNota2());
			ps.setDouble(7, alumno.getNota3());
			ps.setDouble(8, alumno.getNota_socio());
			ps.setDouble(9, alumno.getNota_final());
			ps.setDouble(10, alumno.getAuto_evaluacion());
			ps.setBoolean(11, alumno.getEliminado());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> crearAlumnoCurso -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int eliminarAlumnosCurso(CursoTO curso) {
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "DELETE FROM alumno WHERE id_curso = ?;";
		
		try{
			ps = this.conn.prepareStatement(query);
			ps.setInt(1, curso.getId_curso());
			result = ps.executeUpdate();
			ps.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> eliminarAlumnosCurso -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public int crearCurso(CursoTO curso) {
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "INSERT INTO curso( "+ CAMPOS_CURSO + " )"+
					   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, curso.getId_curso());
			ps.setInt(2, curso.getAnio());
			ps.setInt(3, curso.getSemestre());
			ps.setString(4, curso.getSeccion());
			ps.setString(5, curso.getCarrera());
			ps.setInt(6, curso.getRut_docente());
			ps.setString(7, curso.getNombre_curso());
			ps.setInt(8, curso.getCantidad_alumnos());
			ps.setBoolean(9, curso.getEliminado());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> crearCurso -> "+ ex.getMessage());
		}
		
		return result;
	}
	
	public int modificarCurso(CursoTO curso)
	{
		PreparedStatement ps = null;
		int result = -1;
		
		String query = " UPDATE curso SET "+
					   " semestre=?, seccion=?, carrera=?, rut_docente=?, "+
					   " nombre_curso=?, cantidad_alumnos=?, eliminado=?, anio=? "+
					   " WHERE id_curso=?;";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, curso.getSemestre());
			ps.setString(2, curso.getSeccion());
			ps.setString(3, curso.getCarrera());
			ps.setInt(4, curso.getRut_docente());
			ps.setString(5, curso.getNombre_curso());
			ps.setInt(6, curso.getCantidad_alumnos());
			ps.setBoolean(7, curso.getEliminado());
			ps.setInt(8, curso.getAnio());
			ps.setInt(9, curso.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> modificarCurso -> "+ ex.getMessage());
		}
		
		return result;
	}
	
	public int eliminarCurso(CursoTO curso) {
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "DELETE FROM curso WHERE id_curso = ?;";
		
		try{
			ps = this.conn.prepareStatement(query);
			ps.setInt(1, curso.getId_curso());
			result = ps.executeUpdate();
			ps.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error en la clase DocenteDB -> eliminarCurso -> "+ ex.getMessage());
		}		
		return result;
	}
	
	public List<CursoTO> obtenerTodosCurso()
	{
		List<CursoTO> listaCurso = new ArrayList<CursoTO>();
		PreparedStatement pstmt = null;		
		ResultSet rs = null;		
		String query = "SELECT "+CAMPOS_CURSO+
					   "FROM curso;";
		
		try
		{
			pstmt = conn.prepareStatement(query);			
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaCurso.add(this.resultSet_a_CursoTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerTodosCurso : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return listaCurso;
	}
	public List<CursoTO> obtenerTodosCursoDocente(UsuarioTO usuario)
	{
		List<CursoTO> listaCurso = new ArrayList<CursoTO>();
		PreparedStatement pstmt = null;		
		ResultSet rs = null;		
		String query = " SELECT "+CAMPOS_CURSO+
					   " cantidad_alumnos, eliminado, anio "+
					   " FROM curso WHERE rut_docente = ? " +
					   " ORDER BY nombre_curso ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, usuario.getRut());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaCurso.add(this.resultSet_a_CursoTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerTodosCursoDocente : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return listaCurso;
	}
	
	public List<GaeTO> obtenerGaesCurso(CursoTO curso)
	{
		List<GaeTO> listaGrupos = new ArrayList<GaeTO>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT "+CAMPOS_GAE+
					   " FROM gae "+
					   " WHERE id_curso = ?" +
					   " ORDER BY nombre_gae ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, curso.getId_curso());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaGrupos.add(this.resultSet_a_GaeTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerGaesCurso : " + sqle.getMessage());
		}
		
		return listaGrupos;
	}
	
	public List<AlumnoTO> obtenerAlumnosCurso(CursoTO curso)
	{
		List<AlumnoTO> alumnosCurso = new ArrayList<AlumnoTO>();
		
		String query = " SELECT "+ CAMPOS_ALUMNO +
					   " FROM alumno WHERE id_curso = ? " +
					   " ORDER BY apellido";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, curso.getId_curso());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				alumnosCurso.add(this.resultSet_a_AlumnoTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerAlumnosCurso : " + sqle.getMessage());
		}
		return alumnosCurso;
	}
	
	public List<AlumnoTO> obtenerAlumnosGae(GaeTO gae)
	{
		List<AlumnoTO> alumnosGae = new ArrayList<AlumnoTO>();
		
		String query = " SELECT "+ CAMPOS_ALUMNO +
					   " FROM alumno WHERE id_gae = ? " +
					   " ORDER BY apellido";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, gae.getId_gae());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				alumnosGae.add(this.resultSet_a_AlumnoTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerAlumnosGae : " + sqle.getMessage());
		}
		return alumnosGae;
	}
	
	
	//id_proyecto, no es insertados,
	//dado que inicialmente no se asocia a un proyecto.
	public int crearGAE(GaeTO gae) {
		
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "INSERT INTO gae( "+
	            	   "id_gae, rut_gae, nombre_gae, id_curso, "+ 
	            	   "cantidad_alumnos, nombre_representante, apellido_representante, matricula_representante, tipo_fono, fono, correo, "+ 
	            	   "nota1, nota2, nota3, nota_socio, nota_final, eliminado_gae) "+
	            	   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, gae.getRut_gae());
			ps.setString(3, gae.getNombre_gae());
			//ps.setInt(4, gae.getId_proyecto());
			ps.setInt(4, gae.getId_curso());
			ps.setInt(5, gae.getCtd_alumno());
			ps.setString(6, gae.getNombreRepresentante());
			ps.setString(7, gae.getApellidoRepresentante());
			ps.setInt(8, gae.getMatriculaRepresentante());
			ps.setString(9, gae.getTipo_fono());
			ps.setInt(10, gae.getFono());
			ps.setString(11, gae.getCorreo());
			ps.setDouble(12, gae.getNota1());
			ps.setDouble(13, gae.getNota2());
			ps.setDouble(14, gae.getNota3());
			ps.setDouble(15, gae.getNota_socio());
			ps.setDouble(16, gae.getNota_final());
			ps.setBoolean(17, gae.getEliminado());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase DocenteDB -> crearGAE -> "+ ex.getMessage());
		}		
		return result;
	}
	
	
	public int modificarGAE(GrupoTO grupo) {
		return 0;
	}
	
	
	public int eliminarGAE(GrupoTO grupo) {
		return 0;
	}
	
	public int reAsignar(UsuarioTO usuarioNuevo, GaeTO gaeOriginal){
		return 0;
	}
	
	public int asignarAlumnoRepresentant(GaeTO gae){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "UPDATE alumno "+
					   "SET id_gae=?, rut_gae=?, tipo_fono=?, fono=?, correo=? "+
					   "WHERE matricula=? AND id_curso=?";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, gae.getRut_gae());
			ps.setString(3, gae.getTipo_fono());
			ps.setInt(4, gae.getFono());
			ps.setString(5, gae.getCorreo());
			ps.setInt(6, gae.getMatriculaRepresentante());
			ps.setInt(7, gae.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase DocenteDB -> asignarAlumnoRepresentant -> "+ ex.getMessage());
		}		
		return result;
	}

	public int asignarAlumnoGae(GaeTO gae, AlumnoTO alumno){
		PreparedStatement ps = null;
		int result = -1;
		
		String query = "UPDATE alumno "+
					   "SET id_gae=? "+
					   "WHERE matricula=? AND id_curso=?";
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, gae.getId_gae());
			ps.setInt(2, alumno.getMatricula());
			ps.setInt(3, gae.getId_curso());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase DocenteDB -> asignarAlumnoGae -> "+ ex.getMessage());
		}		
		return result;
	}
	*/
}
